模拟主库创建数据文件，dg备库空间不足时问题处理-白红宇

模拟主库创建数据文件，dg备库空间不足时问题处理

阅读量：444 次

发布时间：2019-03-06

本文共 13097 字，大约阅读时间需要 43 分钟。

本篇文档测试目的：

模拟实际环境中，主库对表空间添加数据文件，备库空间不足，最终导致MRP进程自动断开，处理方式。

1.问题环境模拟

1）正常情况下的dg主库创建数据文件，备库接受日志，自动创建表空间及数据文件。RFS[49]: Selected log 4 for thread 1 sequence 115 dbid 699220720 branch 994543603Fri Feb 22 23:20:36 2019Media Recovery Log /u01/app/oracle/oradata/arch/1_112_994543603.arcRecovery created file /u01/app/oracle/oradata/adg1/test001.dbfSuccessfully added datafile 10 to media recoveryDatafile #10: '/u01/app/oracle/oradata/adg1/test001.dbf'Media Recovery Log /u01/app/oracle/oradata/arch/1_113_994543603.arcMedia Recovery Log /u01/app/oracle/oradata/arch/1_114_994543603.arcMedia Recovery Waiting for thread 1 sequence 115 (in transit)Fri Feb 22 23:20:52 2019RFS[49]: Selected log 5 for thread 1 sequence 116 dbid 699220720 branch 994543603Fri Feb 22 23:20:52 2019Archived Log entry 51 added for thread 1 sequence 115 ID 0x29aceaf0 dest 1:Fri Feb 22 23:20:52 2019Media Recovery Log /u01/app/oracle/oradata/arch/1_115_994543603.arcMedia Recovery Waiting for thread 1 sequence 116 (in transit)2）异常情况 备库文件系统

[oracle@11gtest arch]$ df -h

文件系统容量已用可用已用% 挂载点

/dev/mapper/VolGroup00-Lvroot

34G 22G 11G 69% / 空间剩余11G

主库创建测试表空间，数据文件大小12G（测试环境ssd）

SQL> create tablespace test_tbs datafile '/home/oracle/test_tbs01.dbf' size 12g;

Tablespace created.

Elapsed: 00:01:50.86

SQL> alter system switch logfile

;

备库Alert日志

Fri Feb 22 23:28:52 2019

RFS[49]: Selected log 4 for thread 1 sequence 117 dbid 699220720 branch 994543603

Fri Feb 22 23:28:52 2019

Archived Log entry 52 added for thread 1 sequence 116 ID 0x29aceaf0 dest 1:

Fri Feb 22 23:28:52 2019

Media Recovery Log /u01/app/oracle/oradata/arch/1_116_994543603.arc

-------------------------对于DG备库而言，最开始是接受日志，MRP进程应用日志，随后空间确实不足后，停止创建数据文件

SQL>

select process,client_process,sequence#,status,BLOCK#,BLOCKS from v$managed_standby

PROCESS CLIENT_P SEQUENCE# STATUS BLOCK# BLOCKS

--------- -------- ---------- ------------ ---------- ----------

MRP0 N/A 116 APPLYING_LOG 3314 4568

[root@11gtest etc]# df -h

文件系统容量已用可用已用% 挂载点

/dev/mapper/VolGroup00-Lvroot

34G 33G 7.2M 100% /

[root@11gtest etc]# df -h 从11g可用空间，将为0，最后又还原为可用空间11g，都是自动进行。

文件系统容量已用可用已用% 挂载点

/dev/mapper/VolGroup00-Lvroot

34G 22G 11G 69% /

---Alert报错

Fri Feb 22 23:28:52 2019

RFS[49]: Selected log 4 for thread 1 sequence 117 dbid 699220720 branch 994543603

Fri Feb 22 23:28:52 2019

Archived Log entry 52 added for thread 1 sequence 116 ID 0x29aceaf0 dest 1:

Fri Feb 22 23:28:52 2019

Media Recovery Log /u01/app/oracle/oradata/arch/1_116_994543603.arc

Fri Feb 22 23:29:51 2019

Errors in file /u01/app/oracle/diag/rdbms/adg1/adg1/trace/adg1_pr00_7496.trc:

ORA-19502: write error on file "/u01/app/oracle/oradata/adg1/test_tbs01.dbf", block number 1337472 (block size=8192)

ORA-27072: File I/O error

Additional information: 4

Additional information: 1337472

Additional information: 577536

File #11 added to control file as 'UNNAMED00011'.

Originally created as:

'/home/oracle/test_tbs01.dbf'

Recovery was unable to create the file as:

'/u01/app/oracle/oradata/adg1/test_tbs01.dbf'

Errors with log /u01/app/oracle/oradata/arch/1_116_994543603.arc

MRP0: Background Media Recovery terminated with error 1274

Errors in file /u01/app/oracle/diag/rdbms/adg1/adg1/trace/adg1_pr00_7496.trc:

ORA-01274: cannot add datafile '/home/oracle/test_tbs01.dbf' - fFri Feb 22 23:29:54 2019

MRP0: Background Media Recovery process shutdown (adg1)

MRP进程自动shutdown：

尝试启动Mrp进程

recover managed standby database disconnect from session;

Fri Feb 22 23:32:52 2019

ALTER DATABASE RECOVER managed standby database disconnect from session

Attempt to start background Managed Standby Recovery process (adg1)

Fri Feb 22 23:32:52 2019

MRP0 started with pid=20, OS id=8449

MRP0: Background Managed Standby Recovery process started (adg1)

started logmerger process

Fri Feb 22 23:32:57 2019

Managed Standby Recovery not using Real Time Apply

Fri Feb 22 23:32:57 2019

Errors in file /u01/app/oracle/diag/rdbms/adg1/adg1/trace/adg1_dbw0_7052.trc:

ORA-01186: file 11 failed verification tests

ORA-01157: cannot identify/lock data file 11 - see DBWR trace file

ORA-01111: name for data file 11 is unknown - rename to correct file

ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011'

File 11 not verified due to error ORA-01157

MRP0: Background Media Recovery terminated with error 1111

Errors in file /u01/app/oracle/diag/rdbms/adg1/adg1/trace/adg1_pr00_8453.trc:

ORA-01111: name for data file 11 is unknown - rename to correct file

ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011'

ORA-01157: cannot identify/lock data file 11 - see DBWR trace file

ORA-01111: name for data file 11 is unknown - rename to correct file

ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011'

Completed: ALTER DATABASE RECOVER managed standby database disconnect from session

Recovery Slave PR00 previously exited with exception 1111

MRP0: Background Media Recovery process shutdown (adg1)

2.问题处理

问题处理方法论，1.立即对空间扩容； 2.找到存在空间空间的路径（磁盘组）先存放一阵子； 3.nfs等文件系统临时挂载存放 本次模拟，使用第二种，找到存在的空闲空间 [root@11gtest software]# rm p13390677_112040_Linux-x86-64_*rm：是否删除 一般文件 “p13390677_112040_Linux-x86-64_1of7.zip”? yrm：是否删除 一般文件 “p13390677_112040_Linux-x86-64_2of7.zip”? y[root@11gtest software]# df -h文件系统              容量  已用 可用 已用% 挂载点/dev/mapper/VolGroup00-Lvroot                       34G   20G   13G  61% /

SQL> select file#,name,status,bytes/1024/1024/1024 g from v$datafile;

FILE# NAME STATUS G

---------- ----------------------------------------------------------------- ------- ----------

1 /u01/app/oracle/oradata/adg1/system01.dbf SYSTEM 2.02148438

2 /u01/app/oracle/oradata/adg1/sysaux01.dbf ONLINE .60546875

3 /u01/app/oracle/oradata/adg1/undotbs01.dbf ONLINE .25390625

4 /u01/app/oracle/oradata/adg1/users01.dbf ONLINE .337646484

5 /u01/app/oracle/oradata/adg1/test01.dbf ONLINE .004150391

6 /u01/app/oracle/oradata/adg1/user02.dbf ONLINE .009765625

7 /u01/app/oracle/oradata/adg1/ogg.dbf ONLINE .009765625

8 /u01/app/oracle/oradata/adg1/test1.dbf ONLINE .059570313

9 /u01/app/oracle/oradata/adg1/test2.dbf ONLINE .010742188

10 /u01/app/oracle/oradata/adg1/test001.dbf ONLINE .000976563

11 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011 RECOVER 0

11 rows selected.

新的数据文件在dba_data_files都不存在。

SQL> select file_name,file_id,tablespace_name,bytes/1024/1024/1024 g,status from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME G STATUS

--------------------------------------------- ---------- ------------------------------ ---------- ---------

/u01/app/oracle/oradata/adg1/users01.dbf 4 USERS .337646484 AVAILABLE

/u01/app/oracle/oradata/adg1/undotbs01.dbf 3 UNDOTBS1 .25390625 AVAILABLE

/u01/app/oracle/oradata/adg1/sysaux01.dbf 2 SYSAUX .60546875 AVAILABLE

/u01/app/oracle/oradata/adg1/system01.dbf 1 SYSTEM 2.02148438 AVAILABLE

/u01/app/oracle/oradata/adg1/test01.dbf 5 AUDITING .004150391 AVAILABLE

/u01/app/oracle/oradata/adg1/user02.dbf 6 USERS .009765625 AVAILABLE

/u01/app/oracle/oradata/adg1/ogg.dbf 7 OGG .009765625 AVAILABLE

/u01/app/oracle/oradata/adg1/test1.dbf 8 IMAGE_APP_TBS .059570313 AVAILABLE

/u01/app/oracle/oradata/adg1/test2.dbf 9 IMAGE_APP_IDX_TBS .010742188 AVAILABLE

/u01/app/oracle/oradata/adg1/test001.dbf 10 TEST001 .000976563 AVAILABLE

10 rows selected.

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011' as '/home/oracle/test_temp00028';

alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011' as '/home/oracle/test_temp00028'

ERROR at line 1:

ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management is automatic.

SQL> alter system set standby_file_management=MANUAL;

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011' as '/home/oracle/test_temp00028';

Database altered.

Fri Feb 22 23:50:35 2019

ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;

Fri Feb 22 23:50:46 2019

alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011' as '/home/oracle/test_temp00028'

Fri Feb 22 23:51:23 2019

Completed: alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00011' as '/home/oracle/test_temp00028'

备库开启MRP进程

SQL> recover managed standby database disconnect from session;

3.后续处理

移动文件后，原有空间足够需要转移，DG数据文件迁移

正确流程操作：数据库启动到Mount阶段rman进行backup as copy方式拷贝数据文件swich 修改控制文件数据文件目录open数据库开启Mrp进程，恢复dg应用rman删除copy备份信息

SQL> startup force mount;

RMAN> backup as copy datafile 11 format '/u01/app/oracle/oradata/adg1/test_tbs01.dbf';

RMAN> switch datafile 11 to copy;

SQL> alter database open; recover managed standby database disconnect from session;

alter database recover managed standby database cancel;

recover managed standby database using current logfile disconnect;

RMAN> list copy of database;RMAN> delete copy of datafile 11;

*************************************** SQL> startup force mount;RMAN> backup as copy datafile 11 format '/u01/app/oracle/oradata/adg1/test_tbs01.dbf';Starting backup at 22-FEB-19using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=20 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00010 name=/u01/app/oracle/oradata/adg1/test001.dbfoutput file name=/u01/app/oracle/oradata/adg1/test_tbs01.dbf tag=TAG20190222T235621 RECID=11 STAMP=1000943781channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 22-FEB-19RMAN-00571: ===========================================================RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/23/2019 00:08:45ORA-19502: write error on file "/u01/app/oracle/oradata/adg1/test_tbs01.dbf", block number 81536 (block size=8192)ORA-27072: File I/O errorAdditional information: 4Additional information: 81536Additional information: 53248ORA-19502: write error on file "/u01/app/oracle/oradata/adg1/test_tbs01.dbf", block number 81536 (block size=8192)空间不足，由于是测试环境，因此对datafile 11进行resize回收空间，SQL> select file_id,bytes/1024/1024/1024 from dba_data_files where file_id=11;FILE_ID BYTES/1024/1024/1024---------- --------------------11 12Elapsed: 00:00:00.00SQL> alter database datafile 11 resize 5g;Database altered.Elapsed: 00:00:01.40SQL> select file_id,bytes/1024/1024/1024 from dba_data_files where file_id=11;FILE_ID BYTES/1024/1024/1024---------- --------------------11 5SQL> alter system switch logfile ;关库，dg环境启动到Mount状态SQL> recover managed standby database disconnect from session;Media recovery complete.SQL> select file_id,bytes/1024/1024/1024 from dba_data_files where file_id=11;FILE_ID BYTES/1024/1024/1024---------- --------------------11 5继续RMAN RMAN> backup as copy datafile 11 format '/u01/app/oracle/oradata/adg1/test_tbs01.dbf';Starting backup at 23-FEB-19using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=20 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00011 name=/home/oracle/test_temp00028output file name=/u01/app/oracle/oradata/adg1/test_tbs01.dbf tag=TAG20190223T001911 RECID=12 STAMP=1000945177channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35Finished backup at 23-FEB-19RMAN> switch datafile 11 to copy;RMAN-06572: database is open and datafile 11 is not offline switch datafile 修改数据文件目录操作，需要数据文件offline,数据库需要启动到mount阶段。RMAN> switch datafile 11 to copy;using target database control file instead of recovery catalogdatafile 11 switched to datafile copy "/u01/app/oracle/oradata/adg1/test_tbs01.dbf"RMAN> list copy of database;RMAN> delete copy of datafile 11;SQL> alter database open;Database altered.SQL> r1* select file_id,file_name,tablespace_name,bytes/1024/1024/1024 from dba_data_filesFILE_ID FILE_NAME TABLESPACE_NAME BYTES/1024/1024/1024------- ------------------------------------------------------- ------------------------------ --------------------4 /u01/app/oracle/oradata/adg1/users01.dbf USERS .3376464843 /u01/app/oracle/oradata/adg1/undotbs01.dbf UNDOTBS1 .253906252 /u01/app/oracle/oradata/adg1/sysaux01.dbf SYSAUX .605468751 /u01/app/oracle/oradata/adg1/system01.dbf SYSTEM 2.021484385 /u01/app/oracle/oradata/adg1/test01.dbf AUDITING .0041503916 /u01/app/oracle/oradata/adg1/user02.dbf USERS .0097656257 /u01/app/oracle/oradata/adg1/ogg.dbf OGG .0097656258 /u01/app/oracle/oradata/adg1/test1.dbf IMAGE_APP_TBS .0595703139 /u01/app/oracle/oradata/adg1/test2.dbf IMAGE_APP_IDX_TBS .01074218810 /u01/app/oracle/oradata/adg1/test001.dbf TEST00111 /u01/app/oracle/oradata/adg1/test_tbs01.dbf TEST_TBS 511 rows selected.alter tablespace TEST_TBS rename datafile '/home/oracle/test_temp00028' to '/u01/app/oracle/oradata/adg1/test001.dbf'*ERROR at line 1:ORA-16000: database open for read-only accessSQL> alter database create datafile '/home/oracle/test_temp00028' as '/u01/app/oracle/oradata/adg1/test001.dbf';alter database create datafile '/home/oracle/test_temp00028' as '/u01/app/oracle/oradata/adg1/test001.dbf'*ERROR at line 1:ORA-01524: cannot create data file as'/u01/app/oracle/oradata/adg1/test001.dbf' - file already part of database

转载地址：http://yilyz.baihongyu.com/

你可能感兴趣的文章

MySQL 大数据量快速插入方法和语句优化

mysql 字段合并问题（group_concat）

查看>>

mysql 字段类型类型

查看>>